<HTML>
    <HEAD>
        <TITLE>Genesys Team Report ver 2.0. (c)Pavel Usim</TITLE>
        <link rel="stylesheet" type="text/css" href="..\def.css" />
    </HEAD>
    <BODY>
        <img align ="right" alt ="Genesys" src="..\img\GenesysLogo.png">
        <p align="center"><a name="top">Genesys Team Report. ver 2.0</a><img src="..\img\beta.gif" alt="beta">
            <br>Admin interface.</p>

        <?php
        ini_set("max_execution_time", "90000");
        /*
          Source Data Processing
          @author Koldun
         *
          - mysql password
          - admin password
          - post var verification
          - hack attemps

          - report by products...;
          - T3 report
          - what progress after export??? sep table?

          Sorted in medium
          report for All Tier-3
          CustSat by Poducts     -done
          quick links on top     -done
          Closed by Priority, 
          closed by Resolution,  -done
          closed by Products.    -done
         *
         *          */

        require '..\stt.php';

        function get_date($date1) {
            if ($date1 == '')
                return 0;
            if (strpos($date1, '.')) {       // 02.03.2009 12:59
                $date1 = explode(' ', $date1);
                $date2 = explode('.', $date1[0]);
                $date1 = explode(':', $date1[1]);
                $date1 = mktime($date1[0], $date1[1], 0, $date2[0], $date2[1], $date2[2]);
            } else {                            // 1/15/2009 02:38:19 PM
                $date3 = explode(' ', $date1);
                $date2 = explode('/', $date3[0]);
                $date1 = explode(':', $date3[1]);
                if ($date3[2] == 'PM')
                    $date1[0] += 12;
                $date1 = mktime($date1[0], $date1[1], $date1[2], $date2[0], $date2[1], $date2[2]);
            }
            return $date1;
        }

// get_date()

        function setnull($string) {
            if ($string == '')
                $string = 'null';
            else
                $string = '\'' . $string . '\'';
            return $string;
        }

        require '..\dbconnect.php';

        $dirh = opendir(getcwd());
        while ($files = readdir($dirh)) {

            // srs source file processing
            if (( strtolower(substr($files, strlen($files) - 3, 3)) == 'txt')) {
                $fp = fopen($files, "r");
                $vector = explode(Chr(9), chop(fgets($fp)));

                if ($vector[0] <> 'Escalation Flag')
                    die('Escalation Flag header is missing in ' . $files);
                if ($vector[1] <> 'Date Closed')
                    die('Date Closed header is missing in ' . $files);
                if ($vector[2] <> 'Date Opened')
                    die('Date Opened header is missing in ' . $files);
                if ($vector[3] <> 'Priority')
                    die('Priority header is missing in ' . $files);
                if ($vector[4] <> 'Owner')
                    die('Owner header is missing in ' . $files);
                if ($vector[5] <> 'SR #')
                    die('SR # header is missing in ' . $files);
                if ($vector[6] <> 'SR Status')
                    die('SR Status header is missing in ' . $files);
                if ($vector[7] <> 'SR Substatus')
                    die('SR Substatus header is missing in ' . $files);
                if ($vector[8] <> 'Tier 3 Owner')
                    die('Tier 3 Owner header is missing in ' . $files);
                if ($vector[9] <> 'Last Public Activity')
                    die('Last Public Activity header is missing in ' . $files);
                if ($vector[10] <> 'Date Modified')
                    die('Date Modified header is missing in ' . $files);
                if ($vector[11] <> 'Sold To Account')
                    die('Sold To Account header is missing in ' . $files);
                if ($vector[12] <> 'End User Account')
                    die('End User Account header is missing in ' . $files);
                if ($vector[13] <> 'Owner Support Group')
                    die('Owner Support Group header is missing in ' . $files);
                if ($vector[14] <> 'End User Support Group')
                    die('End User Support Group header is missing in ' . $files);
                if ($vector[15] <> 'Product')
                    die('Product header is missing in ' . $files);
                if ($vector[16] <> 'Product Line')
                    die('Product Line header is missing in ' . $files);
                if ($vector[17] <> 'Sub Area')
                    die('Sub Area header is missing in ' . $files);
                if ($vector[18] <> 'Version')
                    die('Version header is missing in ' . $files);
                if ($vector[20] <> 'Production Down')
                    die('Production Down header is missing in ' . $files);
                if ($vector[21] <> 'O/S')
                    die('O/S header is missing in ' . $files);
                if ($vector[22] <> 'Switch')
                    die('Switch header is missing in ' . $files);
                if ($vector[23] <> 'Database')
                    die('Database header is missing in ' . $files);
                if ($vector[25] <> 'Training Flag')
                    die('Training Flag header is missing in ' . $files);
                if ($vector[26] <> 'Resolution Code')
                    die('Resolution Code header is missing in ' . $files);
                if ($vector[27] <> 'Qualified')
                    die('Qualified header is missing in ' . $files);
                if ($vector[28] <> 'Contact Last Name')
                    die('Contact Last Name header is missing in ' . $files);
                if ($vector[29] <> 'Contact First Name')
                    die('Contact First Name header is missing in ' . $files);

                while ($chunk = fgets($fp)) {

                    $chunk = explode(Chr(9), chop($chunk));
                    //require '../stt.php';

                    foreach ($vector as $key => $value)
                        $cur_vector[$value] = addslashes($chunk[$key]);

                    //verificaton on initial records in source file with notification.
                    if ($cur_vector['Escalation Flag'] <> 'Y' and $cur_vector['Escalation Flag'] <> 'N')
                        die('Incorrect source file: ' . $files);

                    $cur_vector['SR #'] = str_replace(',', '.', $cur_vector['SR #']);
                    $cur_vector['SR #'] = $cur_vector['SR #'] * 1000;

                    // TODO: fix an issue with FlexLM version
                    // TODO: use one query to select all the data into an arrey... IF POSSIBLE

                    if ($cur_vector['Date Closed'] <> '')
                        $cur_vector['Date Closed'] = get_date($cur_vector['Date Closed']);
                    else
                        $cur_vector['Date Closed'] = 'null';

                    // verification on RE-CLOSED SRs, saved into separate table!
                    $query = 'select closed, tse_name from srs, tse where sr =' . $cur_vector['SR #'] . ' and owner = owner_id';
                    $result = mysql_query($query);
                    $res = mysql_fetch_assoc($result);
                    if (mysql_num_rows($result) > 0 and $res['closed'] <> '' and $res['closed'] <> $cur_vector['Date Closed']) {
                        if ($cur_vector['Date Closed'] == 'null')
                            $tmp = 'OPEN'; else
                            $tmp= date("j.n.y", $cur_vector['Date Closed']);
                        echo ' date closed is changed for ' . $res['tse_name'] . ' in - ' . $cur_vector['SR #'] . ' from ' . date("j.n.y", $res['closed']) . ' to ' . $tmp . '<br>';
                        mysql_query('insert into checksr values(' . $cur_vector['SR #'] . ')') or die('unable to insert ' . $cur_vector['SR #'] . ' into checksr table!');
                    }
                    mysql_free_result($result);

                    $cur_vector['Date Opened'] = get_date($cur_vector['Date Opened']);
                    $cur_vector['Priority'] = substr($cur_vector['Priority'], 0, 1);
                    $cur_vector['Owner'] = str_replace(',', '', $cur_vector['Owner']);

                    // Updating Dictionary
                    // get Owner ID
                    if ($cur_vector['Owner'] <> '') {
                        $result = mysql_query('select owner_id from tse where tse_name = \'' . $cur_vector['Owner'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into tse (tse_name,team,email) values(\'' . $cur_vector['Owner'] . '\', \'na\', null)';
                            mysql_query($query) or die('Query failed (' . $query . ' at' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select owner_id from tse where tse_name = \'' . $cur_vector['Owner'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Owner'] = $res['owner_id'];
                    } else
                        $cur_vector['Owner'] = 'null';

                    // get SR status
                    $result = mysql_query('select id from srstatus where val = \'' . $cur_vector['SR Status'] . '\'');
                    if (mysql_num_rows($result) == 0) {
                        $query = 'insert into srstatus (val) values(\'' . $cur_vector['SR Status'] . '\')';
                        mysql_query($query) or die('Query failed (' . $query . ' at' . $files . ') : ' . mysql_error());
                        $result = mysql_query('select id from srstatus where val = \'' . $cur_vector['SR Status'] . '\'');
                    }
                    $res = mysql_fetch_assoc($result);
                    mysql_free_result($result);
                    $cur_vector['SR Status'] = $res['id'];

                    // get SR Substatus
                    $result = mysql_query('select id from srsubstatus where val = \'' . $cur_vector['SR Substatus'] . '\'');
                    if (mysql_num_rows($result) == 0) {
                        $query = 'insert into srsubstatus (val) values(\'' . $cur_vector['SR Substatus'] . '\')';
                        mysql_query($query) or die('Query failed (' . $query . ' at' . $files . ') : ' . mysql_error());
                        $result = mysql_query('select id from srsubstatus where val = \'' . $cur_vector['SR Substatus'] . '\'');
                    }
                    $res = mysql_fetch_assoc($result);
                    mysql_free_result($result);
                    $cur_vector['SR Substatus'] = $res['id'];

                    // get Tier-3 owner
                    // if tier3 owner lost - add to cheaters...
                    $query = 'select tse.tse_name from srs, tse where srs.sr = ' . $cur_vector['SR #'] . ' and tier_t_owner = owner_id';
                    $result = mysql_query($query);
                    $res = mysql_fetch_assoc($result);
                    if (mysql_num_rows($result) > 0 and $cur_vector['Tier 3 Owner'] == '' and $res['tse_name'] <> '') {
                        echo 'Tier-3 lost in - ' . $cur_vector['SR #'] . ' by ' . $cur_vector['Owner'] . '<br>';
                        mysql_query('insert into checksr values(' . $cur_vector['SR #'] . ')') or die('unable to insert ' . $cur_vector['SR #'] . ' into checksr table!');
                    }
                    mysql_free_result($result);

                    if ($cur_vector['Tier 3 Owner'] <> '') {
                        $cur_vector['Tier 3 Owner'] = str_replace(',', '', $cur_vector['Tier 3 Owner']);
                        $result = mysql_query('select owner_id from tse where tse_name = \'' . $cur_vector['Tier 3 Owner'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into tse (tse_name, team, email) values(\'' . $cur_vector['Tier 3 Owner'] . '\', \'t3\', null)';
                            mysql_query($query) or die('Query failed (' . $query . ' at' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select owner_id from tse where tse_name = \'' . $cur_vector['Tier 3 Owner'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Tier 3 Owner'] = $res['owner_id'];
                    } else
                        $cur_vector['Tier 3 Owner'] = 'null';



                    $cur_vector['Last Public Activity'] = setnull(get_date($cur_vector['Last Public Activity']));
                    $cur_vector['Date Modified'] = get_date($cur_vector['Date Modified']);

                    // get Sold To Account
                    $result = mysql_query('select id from staccount where val = \'' . $cur_vector['Sold To Account'] . '\'');
                    if (mysql_num_rows($result) == 0) {
                        $query = 'insert into staccount (val) values(\'' . $cur_vector['Sold To Account'] . '\')';
                        mysql_query($query) or die('Query failed(' . $query . ' at' . $files . ') : ' . mysql_error());
                        $result = mysql_query('select id from staccount where val = \'' . $cur_vector['Sold To Account'] . '\'');
                    }
                    $res = mysql_fetch_assoc($result);
                    mysql_free_result($result);
                    $cur_vector['Sold To Account'] = $res['id'];

                    // get End User Account
                    $result = mysql_query('select id from euaccount where val = \'' . $cur_vector['End User Account'] . '\'');
                    if (mysql_num_rows($result) == 0) {
                        $query = 'insert into euaccount (val) values(\'' . $cur_vector['End User Account'] . '\')';
                        mysql_query($query) or die('Query failed(' . $query . ' at' . $files . ') : ' . mysql_error());
                        $result = mysql_query('select id from euaccount where val = \'' . $cur_vector['End User Account'] . '\'');
                    }
                    $res = mysql_fetch_assoc($result);
                    mysql_free_result($result);
                    $cur_vector['End User Account'] = $res['id'];

                    // get Region
                    if ($cur_vector['Owner Support Group'] <> '') {
                        $result = mysql_query('select id from supportgroup where val = \'' . $cur_vector['Owner Support Group'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into supportgroup (val) values(\'' . $cur_vector['Owner Support Group'] . '\')';
                            mysql_query($query) or die('Query failed(' . $query . ' at' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select id from supportgroup where val = \'' . $cur_vector['Owner Support Group'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Owner Support Group'] = $res['id'];
                    } else
                        $cur_vector['Owner Support Group'] = 'null';

                    // get Region (End User Support Group)
                    $result = mysql_query('select id from custregion where val = \'' . $cur_vector['End User Support Group'] . '\'');
                    if (mysql_num_rows($result) == 0) {
                        $query = 'insert into custregion (val) values(\'' . $cur_vector['End User Support Group'] . '\')';
                        mysql_query($query) or die('Query failed : ' . mysql_error());
                        $result = mysql_query('select id from custregion where val = \'' . $cur_vector['End User Support Group'] . '\'');
                    }
                    $res = mysql_fetch_assoc($result);
                    mysql_free_result($result);
                    $cur_vector['End User Support Group'] = $res['id'];

                    // get Product
                    if ($cur_vector['Product'] <> '') {
                        $result = mysql_query('select id from product where val = \'' . $cur_vector['Product'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into product (val) values(\'' . $cur_vector['Product'] . '\')';
                            mysql_query($query) or die('Query failed(' . $query . ' at' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select id from product where val = \'' . $cur_vector['Product'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Product'] = $res['id'];
                    } else
                        $cur_vector['Product'] = 'null';

                    // get Product Line
                    if ($cur_vector['Product Line'] <> '') {
                        $result = mysql_query('select id from productline where val = \'' . $cur_vector['Product Line'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into productline (val) values(\'' . $cur_vector['Product Line'] . '\')';
                            mysql_query($query) or die('Query failed(' . $query . ' at' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select id from productline where val = \'' . $cur_vector['Product Line'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Product Line'] = $res['id'];
                    } else
                        $cur_vector['Product Line'] = 'null';

                    // get Sub Area
                    if ($cur_vector['Sub Area'] <> '') {
                        $result = mysql_query('select id from subarea where val = \'' . $cur_vector['Sub Area'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into subarea (val) values(\'' . $cur_vector['Sub Area'] . '\')';
                            $result = mysql_query($query) or die('Query failed(' . $query . ' at' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select id from subarea where val = \'' . $cur_vector['Sub Area'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Sub Area'] = $res['id'];
                    } else
                        $cur_vector['Sub Area'] = 'null';

                    // get O/S
                    if ($cur_vector['O/S'] <> '') {
                        $result = mysql_query('select id from os where val = \'' . $cur_vector['O/S'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into os (val) values(\'' . $cur_vector['O/S'] . '\')';
                            mysql_query($query) or die('Query failed(' . $query . ' at' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select id from os where val = \'' . $cur_vector['O/S'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['O/S'] = $res['id'];
                    } else
                        $cur_vector['O/S'] = 'null';

                    // get Switch info
                    if ($cur_vector['Switch'] <> '') {
                        $result = mysql_query('select id from switch where val = \'' . $cur_vector['Switch'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into switch (val) values(\'' . $cur_vector['Switch'] . '\')';
                            mysql_query($query) or die('Query failed(' . $query . ' at' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select id from switch where val = \'' . $cur_vector['Switch'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Switch'] = $res['id'];
                    } else
                        $cur_vector['Switch'] = 'null';

                    // get db info
                    if ($cur_vector['Database'] <> '') {
                        $result = mysql_query('select id from dbms where val = \'' . $cur_vector['Database'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into dbms (val) values(\'' . $cur_vector['Database'] . '\')';
                            mysql_query($query) or die('Query failed(' . $query . ' at' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select id from dbms where val = \'' . $cur_vector['Database'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Database'] = $res['id'];
                    } else
                        $cur_vector['Database'] = 'null';

                    // get Link info
                    if ($cur_vector['Link'] <> '') {
                        $result = mysql_query('select id from link where val = \'' . $cur_vector['Link'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into link (val) values(\'' . $cur_vector['Link'] . '\')';
                            mysql_query($query) or die('Query failed(' . $query . ' at ' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select id from link where val = \'' . $cur_vector['Link'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Link'] = $res['id'];
                    } else
                        $cur_vector['Link'] = 'null';

                    // get Resolution Code
                    if ($cur_vector['Resolution Code'] <> '') {
                        $result = mysql_query('select id from resolcode where val = \'' . $cur_vector['Resolution Code'] . '\'');
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into resolcode (val) values(\'' . $cur_vector['Resolution Code'] . '\')';
                            mysql_query($query) or die('Query failed(' . $query . ' at ' . $files . ') : ' . mysql_error());
                            $result = mysql_query('select id from resolcode where val = \'' . $cur_vector['Resolution Code'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Resolution Code'] = $res['id'];
                    } else
                        $cur_vector['Resolution Code'] = 'null';

                    if ($cur_vector['Contact Last Name'] <> '') {
                        $cur_vector['Contact Last Name'] .= ' ' . $cur_vector['Contact First Name'];
                        $query = 'select id from customer where val = \'' . $cur_vector['Contact Last Name'] . '\'';
                        $result = mysql_query($query);
                        if (mysql_num_rows($result) == 0) {
                            $query = 'insert into customer (val) values(\'' . $cur_vector['Contact Last Name'] . '\')';
                            mysql_query($query) or die('Query failed ' . $query . ' at ' . $files . ' : ' . mysql_error());
                            ;
                            $result = mysql_query('select id from customer where val = \'' . $cur_vector['Contact Last Name'] . '\'');
                        }
                        $res = mysql_fetch_assoc($result);
                        mysql_free_result($result);
                        $cur_vector['Contact Last Name'] = $res['id'];
                    } else
                        $cur_vector['Contact Last Name'] = 'null';


                    $query = 'replace into srs values (' .
                            $cur_vector['SR #'] . ',\'' .
                            $cur_vector['Escalation Flag'] . '\',' .
                            $cur_vector['Date Closed'] . ',' .
                            $cur_vector['Date Opened'] . ',\'' .
                            $cur_vector['Priority'] . '\',' .
                            $cur_vector['Owner'] . ',' .
                            $cur_vector['SR Status'] . ',' .
                            $cur_vector['SR Substatus'] . ',' .
                            $cur_vector['Tier 3 Owner'] . ',' .
                            $cur_vector['Last Public Activity'] . ',' .
                            $cur_vector['Date Modified'] . ',' .
                            $cur_vector['Sold To Account'] . ',' .
                            $cur_vector['End User Account'] . ',' .
                            $cur_vector['Owner Support Group'] . ',' .
                            $cur_vector['End User Support Group'] . ',' .
                            $cur_vector['Product'] . ',' .
                            $cur_vector['Product Line'] . ',' .
                            $cur_vector['Sub Area'] . ',' .
                            setnull($cur_vector['Version']) . ',\'' .
                            $cur_vector['Production Down'] . '\',' .
                            $cur_vector['O/S'] . ',' .
                            $cur_vector['Switch'] . ',' .
                            $cur_vector['Database'] . ',' .
                            $cur_vector['Link'] . ',\'' .
                            $cur_vector['Training Flag'] . '\',' .
                            $cur_vector['Resolution Code'] . ',' .
                            $cur_vector['Contact Last Name'] . ',null,null)';

                    mysql_query($query) or die('Query failed(' . $query . ' at ' . $files . ') : ' . mysql_error());
                } fclose($fp);
            }

            // RCA processing
            if (strtolower(substr($files, strlen($files) - 3, 3)) == 'rca') {
                $fp = fopen($files, "r");
                $vector = explode(Chr(9), chop(fgets($fp)));
                //echo '<pre>'; print_r($vector); echo '</pre>';
                while ($chunk = fgets($fp)) {
                    $chunk = explode(Chr(9), chop($chunk));

                    if ($vector['0'] <> 'Created')
                        die('SR Number header is missing in: ' . $files);
                    if ($vector['1'] <> 'Created By')
                        die('SR Closed header is missing in: ' . $files);
                    if ($vector['2'] <> 'Audience')
                        die('Link Created By header is missing in: ' . $files);
                    if ($vector['3'] <> 'SR Status')
                        die('Solution Doc ID header is missing in: ' . $files);
                    if ($vector['4'] <> 'SR #')
                        die('Usefulness header is missing in: ' . $files);
                    foreach ($vector as $key => $value)
                        $cur_vector[$value] = addslashes($chunk[$key]);

                    $cur_vector['SR #'] = str_replace(',', '.', $cur_vector['SR #']);
                    $cur_vector['SR #'] = $cur_vector['SR #'] * 1000;

                    if ($cur_vector['SR #'] <> 0) {
                        $query = 'update srs set rca = \'Y\' where sr = ' . $cur_vector['SR #'];
                        mysql_query($query) or die('Query failed(' . $query . ' at ' . $files . ') : ' . mysql_error());
                    }
                }
                fclose($fp);
            }

           // Assigned to Tech Lead processing
            if (strtolower(substr($files, strlen($files) - 3, 3)) == 'tla') {
                $fp = fopen($files, "r");
                $vector = explode(Chr(9), chop(fgets($fp)));
                //echo '<pre>'; print_r($vector); echo '</pre>';
                while ($chunk = fgets($fp)) {
                    $chunk = explode(Chr(9), chop($chunk));
                    //echo '<pre>'; print_r($chunk); echo '</pre>';
                    if ($vector['0'] <> 'Created')
                        die('SR Number header is missing in: ' . $files);
                    if ($vector['1'] <> 'Created By')
                        die('SR Closed header is missing in: ' . $files);
                    if ($vector['2'] <> 'Audience')
                        die('Link Created By header is missing in: ' . $files);
                    if ($vector['3'] <> 'SR Status')
                        die('Solution Doc ID header is missing in: ' . $files);
                    if ($vector['4'] <> 'SR #')
                        die('Usefulness header is missing in: ' . $files);
                    foreach ($vector as $key => $value)
                        $cur_vector[$value] = addslashes($chunk[$key]);

                    $cur_vector['SR #'] = str_replace(',', '.', $cur_vector['SR #']);
                    $cur_vector['SR #'] = $cur_vector['SR #'] * 1000;
                    
                    $query = 'update srs set techlead = \'Y\' where sr = ' . $cur_vector['SR #'];
                    mysql_query($query) or die('Query failed(' . $query . ' at ' . $files . ') : ' . mysql_error());
                }
                fclose($fp);
            }


            // KCS source file processing
            if (strtolower(substr($files, strlen($files) - 3, 3)) == 'kcs') {
                $fp = fopen($files, "r");
                $vector = explode(Chr(9), chop(fgets($fp)));
                while ($chunk = fgets($fp)) {
                    $chunk = explode(Chr(9), chop($chunk));

                    if ($vector['0'] <> 'SR Number')
                        die('SR Number header is missing in: ' . $files);
                    if ($vector['1'] <> 'SR Closed')
                        die('SR Closed header is missing in: ' . $files);
                    if ($vector['2'] <> 'Link Created By')
                        die('Link Created By header is missing in: ' . $files);
                    if ($vector['3'] <> 'Solution Doc ID')
                        die('Solution Doc ID header is missing in: ' . $files);
                    if ($vector['4'] <> 'Usefulness')
                        die('Usefulness header is missing in: ' . $files);
                    foreach ($vector as $key => $value)
                        $kcs_vector[$value] = addslashes($chunk[$key]);
//                    
                    if ($kcs_vector['Usefulness'] <> 'Useful' and $kcs_vector['Usefulness'] <> 'Not Used')
                        die('Incorrect source file: ' . $files);

                    // * doc_id varchar(15) NOT NULL,
                    // * sr MEDIUMINT UNSIGNED NOT NULL,
                    $kcs_vector['SR Number'] = str_replace(',', '.', $kcs_vector['SR Number']);
                    $kcs_vector['SR Number'] = $kcs_vector['SR Number'] * 1000;

                    // * linked_by SMALLINT UNSIGNED NOT NULL,
                    $kcs_vector['Link Created By'] = str_replace(',', '', $kcs_vector['Link Created By']);
                    $result = mysql_query('select owner_id from tse where tse_name = \'' . $kcs_vector['Link Created By'] . '\'');
                    if (mysql_num_rows($result) == 0) {
                        $query = 'insert into tse (tse_name, team, email) values(\'' . $kcs_vector['Link Created By'] . '\', \'na\', null)';
                        mysql_query($query) or die('Query failed(' . $query . ' at ' . $files . ') : ' . mysql_error());
                        $result = mysql_query('select owner_id from tse where tse_name = \'' . $kcs_vector['Link Created By'] . '\'');
                    }
                    $res = mysql_fetch_assoc($result);
                    mysql_free_result($result);
                    $kcs_vector['Link Created By'] = $res['owner_id'];
                    // * linked_date INT UNSIGNED,
                    $kcs_vector['Created'] = get_date($kcs_vector['Created']);
                    // * updated_date INT UNSIGNED,
                    $kcs_vector['Updated'] = get_date($kcs_vector['Updated']);

                    // * usefulness ENUM(\'Y\',\'N\'), [Usefulness] => Not Used
                    if ($kcs_vector['Usefulness'] == 'Not Used')
                        $kcs_vector['Usefulness'] = 'N';
                    else
                        $kcs_vector['Usefulness'] = 'Y';

                    // * type ENUM(\'S\',\'D\',\'R\'),)'
                    $type = 'S';
                    if (substr($kcs_vector['Solution Doc ID'], strlen($kcs_vector['Solution Doc ID']) - 3, 3) == 'PDF')
                        $type = 'D';
                    if (substr($kcs_vector['Solution Doc ID'], strlen($kcs_vector['Solution Doc ID']) - 4, 4) == 'HTML')
                        $type = 'R';

                    $query = 'replace into kcs values (\'' .
                            $kcs_vector['Solution Doc ID'] . '\',' .
                            $kcs_vector['SR Number'] . ',' .
                            $kcs_vector['Link Created By'] . ',' .
                            $kcs_vector['Created'] . ',' .
                            $kcs_vector['Updated'] . ',\'' .
                            $kcs_vector['Usefulness'] . '\',\'' .
                            $type . '\')';

                    mysql_query($query) or die('Query failed(' . $query . ' at ' . $files . ') : ' . mysql_error());
                }
                fclose($fp);
            }

            // Customer Satisfaction source file processing
            if (strtolower(substr($files, strlen($files) - 3, 3)) == 'csv') {
                $fp = fopen($files, "r");
                $vector = explode(Chr(9), chop(fgets($fp)));
                while ($chunk = fgets($fp)) {
                    $chunk = explode(Chr(9), chop($chunk));

                    if ($vector[0] <> 'SR #')
                        die('SR # header missed in: ' . $files);
                    if ($vector[1] <> 'SR Owner')
                        die('SR Owner header missed in: ' . $files);
                    if ($vector[2] <> 'Survey Date/Time')
                        die('Survey Date header missed in: ' . $files);
                    if ($vector[3] <> 'Technical skill with Genesys software')
                        die('Skill header missed in: ' . $files);
                    if ($vector[4] <> 'Keeping you advised of the ticket status')
                        die('Advised header missed in: ' . $files);
                    if ($vector[5] <> 'Professionalism')
                        die('Professionalism header missed in: ' . $files);
                    if ($vector[6] <> 'Time to receive resolution')
                        die('Time to resolution header missed in: ' . $files);
                    if ($vector[7] <> 'Effectiveness of resolution')
                        die('Effectivness header missed in: ' . $files);
                    if ($vector[8] <> 'Overall satisfaction')
                        die('Overall header missed in: ' . $files);

                    foreach ($vector as $key => $value)
                        $cs_vector[$value] = addslashes($chunk[$key]);

                    //    sr MEDIUMINT UNSIGNED NOT NULL,
                    $cs_vector['SR #'] = str_replace(',', '.', $cs_vector['SR #']);
                    $cs_vector['SR #'] = $cs_vector['SR #'] * 1000;

                    //    sr_owner SMALLINT UNSIGNED NOT NULL,
                    $cs_vector['SR Owner'] = str_replace(',', '', $cs_vector['SR Owner']);
                    $result = mysql_query('select owner_id from tse where tse_name = \'' . $cs_vector['SR Owner'] . '\'');
                    if (mysql_num_rows($result) == 0) {
                        $query = 'insert into tse (tse_name, team, email) values(\'' . $cs_vector['SR Owner'] . '\', \'na\', null)';
                        mysql_query($query) or die('Query failed(' . $query . ' at ' . $files . ') : ' . mysql_error());
                        $result = mysql_query('select owner_id from tse where tse_name = \'' . $cs_vector['SR Owner'] . '\'');
                    }
                    $res = mysql_fetch_assoc($result);
                    mysql_free_result($result);
                    $cs_vector['SR Owner'] = $res['owner_id'];

                    //    survey_date INT UNSIGNED NOT NULL,
                    $cs_vector['Survey Date/Time'] = get_date($cs_vector['Survey Date/Time']);

                    //    skill ENUM(\'1\',\'2\',\'3\',\'4\',\'5\'),
                    $cs_vector['Technical skill with Genesys software'] = substr($cs_vector['Technical skill with Genesys software'], 0, 1);
                    if ($cs_vector['Technical skill with Genesys software'] > 5)
                        die('(Skill) incorrect source file:' . $files);
                    //    advised ENUM(\'1\',\'2\',\'3\',\'4\',\'5\'),
                    $cs_vector['Keeping you advised of the ticket status'] = substr($cs_vector['Keeping you advised of the ticket status'], 0, 1);
                    if ($cs_vector['Keeping you advised of the ticket status'] > 5)
                        die('(Advised)incorrect source file:' . $files);
                    //    professional ENUM(\'1\',\'2\',\'3\',\'4\',\'5\'),
                    $cs_vector['Professionalism'] = substr($cs_vector['Professionalism'], 0, 1);
                    if ($cs_vector['Professionalism'] > 5)
                        die('(Proffesionalism) incorrect source file:' . $files);
                    //    time ENUM(\'1\',\'2\',\'3\',\'4\',\'5\'),
                    $cs_vector['Time to receive resolution'] = substr($cs_vector['Time to receive resolution'], 0, 1);
                    if ($cs_vector['Time to receive resolution'] > 5)
                        die('(Time) incorrect source file:' . $files);
                    //    efect ENUM(\'1\',\'2\',\'3\',\'4\',\'5\'),
                    $cs_vector['Effectiveness of resolution'] = substr($cs_vector['Effectiveness of resolution'], 0, 1);
                    if ($cs_vector['Effectiveness of resolution'] > 5)
                        die('(Effectivness) incorrect source file:' . $files);
                    //    overall ENUM(\'1\',\'2\',\'3\',\'4\',\'5\'),
                    $cs_vector['Overall satisfaction'] = substr($cs_vector['Overall satisfaction'], 0, 1);
                    if ($cs_vector['Overall satisfaction'] > 5)
                        die('(Overall) incorrect source file:' . $files);
                    $query = 'replace into custsat values (' .
                            $cs_vector['SR #'] . ',' .
                            $cs_vector['SR Owner'] . ',' .
                            $cs_vector['Survey Date/Time'] . ',' .
                            $cs_vector['Technical skill with Genesys software'] . ',' .
                            $cs_vector['Keeping you advised of the ticket status'] . ',' .
                            $cs_vector['Professionalism'] . ',' .
                            $cs_vector['Time to receive resolution'] . ',' .
                            $cs_vector['Effectiveness of resolution'] . ',' .
                            $cs_vector['Overall satisfaction'] . ')';

                    mysql_query($query) or die('Query failed(' . $query . ' at ' . $files . ') : ' . mysql_error());
                } //  survey's file processed (string by string).
                fclose($fp);
            }
        } closedir($dirh);


        require '..\ent.php';
        mysql_close($conn);

        echo '<br>done <a href=index.php>back to root page</a>';
        ?>
    </BODY>
</HTML>
